import pandas as pd
import json
import os


def excel_to_json(excel_file_path, json_file_path=None, sheet_name=0, orient='records'):
    try:
        df = pd.read_excel(excel_file_path, sheet_name=sheet_name)
        df = df.fillna('')
        if json_file_path is None:
            base_name = os.path.splitext(excel_file_path)[0]
            json_file_path = f"{base_name}.json"
        json_data = df.to_json(orient=orient, force_ascii=False, indent=4)
        with open(json_file_path, 'w', encoding='utf-8') as f:
            f.write(json_data)
        print(f"The excel data has been transformed successfully, the json data has been saved to {json_file_path}")
        return True
    except FileNotFoundError:
        print(f"Error, couldn't find the file: {excel_file_path}")
        return False
    except Exception as e:
        print(f"Error happened during the transformation: {str(e)}")
        return False


def convert_excel_to_json(excel_file_path, json_file_path=None, sheet_name=0, orient='records'):
    """
    便捷函数：将Excel文件转换为JSON文件并返回JSON文件路径
    专门设计用于被其他文件导入使用
    
    参数:
    excel_file_path (str): Excel文件的路径
    json_file_path (str, 可选): 输出JSON文件的路径，如果不指定则使用与Excel同名的JSON文件
    sheet_name (str或int, 可选): 要读取的Excel工作表名称或索引，默认为0
    orient (str, 可选): JSON数据的格式，默认为'records'
    
    返回:
    str: JSON文件的路径，如果转换失败则返回None
    """
    try:
        df = pd.read_excel(excel_file_path, sheet_name=sheet_name)
        df = df.fillna('')
        
        # 确定JSON输出路径
        if json_file_path is None:
            base_name = os.path.splitext(excel_file_path)[0]
            json_file_path = f"{base_name}.json"
        
        # 转换为JSON并保存
        json_data = df.to_json(orient=orient, force_ascii=False, indent=4)
        with open(json_file_path, 'w', encoding='utf-8') as f:
            f.write(json_data)
        
        print(f"The excel data has been transformed successfully, the json data has been saved to {json_file_path}")
        return json_file_path
    except FileNotFoundError:
        print(f"Error, couldn't find the file: {excel_file_path}")
        return None
    except Exception as e:
        print(f"Error happened during the transformation: {str(e)}")
        return None


if __name__ == "__main__":
    excel_path = "/Users/mike.chen/Desktop/伪桌面（9-10月）/oop期末_副本/greedy_-snakes_2/Greedy Snakes 2 frame/ali_bao_bao/data/商品数据集.xlsx"
    excel_to_json(excel_path)